﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Xml;
using System.Text.RegularExpressions;
using Data.Abstract;
using Data.Configuration;
using System.Data.SQLite;
namespace Data
{
    public class DalVisitante
    {
        #region Propiedades
        Context _Context = new Context();
        SQLiteConnection _Conn;
        #endregion
        #region Procedimientos
        public DalVisitante() { }
        public DalVisitante(SQLiteConnection conn)
        {
            this._Conn = conn;
        }
        #endregion
        #region Metodos
       
        public void Insert(SimpleVisitante simple)
        {
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                if (!CheckTableExist())
                {
                    CreateTable();
                }
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "INSERT INTO Visitante (UserName,Password,FullName,FechaIngreso)values(@UserName,@Password,@FullName,@FechaIngreso)";

                cmd.Parameters.Add("@Id", DbType.Int32);
                cmd.Parameters.Add("@UserName", DbType.String);
                cmd.Parameters.Add("@Password", DbType.String);
                cmd.Parameters.Add("@FullName", DbType.String);
                cmd.Parameters.Add("@FechaIngreso", DbType.DateTime);

                cmd.Parameters[0].Value = simple.Id;
                cmd.Parameters[1].Value = simple.UserName;
                cmd.Parameters[2].Value = simple.Password;
                cmd.Parameters[3].Value = simple.FullName;
                cmd.Parameters[4].Value = simple.FechaIngreso;
                conn.Open();
                cmd.ExecuteNonQuery();

            }

        }
        public void Update(SimpleVisitante simple)
        {
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                if (CheckTableExist())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "Update Visitante set UserName = @UserName,Password = @Password,FullName = @FullName,FechaIngreso = @FechaIngreso where Id = @Id";

                    cmd.Parameters.Add("@Id", DbType.Int32);
                    cmd.Parameters.Add("@UserName", DbType.String );
                    cmd.Parameters.Add("@Password", DbType.String);
                    cmd.Parameters.Add("@FullName", DbType.String);
                    cmd.Parameters.Add("@FechaIngreso", DbType.DateTime);

                    cmd.Parameters[0].Value = simple.Id;
                    cmd.Parameters[1].Value = simple.UserName;
                    cmd.Parameters[2].Value = simple.Password;
                    cmd.Parameters[3].Value = simple.FullName;
                    cmd.Parameters[4].Value = simple.FechaIngreso;
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void CreateTable()
        {
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "CREATE TABLE Visitante (Id INTEGER PRIMARY KEY AUTOINCREMENT, UserName varchar NOT NULL, Password varchar NOT NULL, FullName varchar NOT NULL,  FechaIngreso datetime NOT NULL)";
                conn.Open();
                cmd.ExecuteNonQuery();
            }


        }
        //public DataTable GetByFecha(DateTime Fecha)
        //{
        //    DataTable Out = new DataTable();
        //    if (CheckTableExist())
        //    {

        //        using (SQLiteConnection conn = this._Conn)
        //        using (SQLiteCommand cmd = new SQLiteCommand(conn))
        //        {
        //            cmd.CommandType = CommandType.Text;
        //            cmd.CommandText = "Select * from Visitante where Fecha = @Fecha ";

        //            cmd.Parameters.Add("@Fecha", DbType.DateTime);
        //            cmd.Parameters[0].Value = Fecha;

        //            conn.Open();
        //            SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
        //            sda.Fill(Out);
                    
        //        }
        //    }
        //    return Out;
        //}
        public DataTable GetAll()
        {
            DataTable Out = new DataTable();

            if (CheckTableExist())
            {
                using (SQLiteConnection conn = this._Conn)
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "Select * from Visitante";
                    conn.Open();
                    SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                    sda.Fill(Out);
                }
            }
            return Out;
        }
        public DataTable GetByName(string Name)
        {
            DataTable Out = new DataTable();

            if (CheckTableExist())
            {
                using (SQLiteConnection conn = this._Conn)
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "Select * from Visitante where UserName = @Username";

                    cmd.Parameters.Add("@Username", DbType.String);
                    cmd.Parameters[0].Value = Name;

                    conn.Open();
                    SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                    sda.Fill(Out);
                }
            }
            return Out;
        }
        public bool CheckTableExist()
        {

            DataTable Out = new DataTable();
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = " SELECT * FROM sqlite_master WHERE name = 'Visitante' AND type = 'table'";
                conn.Open();
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                sda.Fill(Out);
                bool SchemaOk = (Out.Rows.Count == 0) ? false : true;
                return SchemaOk;
            }
        }

        #endregion
    }
}